package com.mine.site.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mine.site.model.Commodity;
import com.mine.site.model.User;
import com.mine.site.model.bsUser;
import com.mine.site.util.DbUtil;


@SuppressWarnings("unused")
public class UserDao {
	    
	
	public List<User> findAll() {
		List<User> users = new ArrayList<User>();
		String sql = "select * from t_user2";
		Statement stmt = null;
		try {
			stmt = DbUtil.getInstance().getConn().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				User u = new User();
				u.setLoginId(rs.getString(1));
				u.setName(rs.getString(2));
				u.setLoginPwd(rs.getString(3));
				u.setPhone(rs.getString(4));
				u.setEmail(rs.getString(5));
				u.setAddress(rs.getString(6));
				u.setMessage(rs.getString(7));
				users.add(u);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return users;
	}
	
	
	public List<Commodity> findclnz() {
		List<Commodity> comm = new ArrayList<Commodity>();
		String sql = "select * from chlz";
		Statement stmt = null;
		try {
			stmt = DbUtil.getInstance().getConn().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Commodity u = new Commodity();
				u.setNumber(rs.getInt(1));
				u.setName(rs.getString(2));
				u.setTitle(rs.getString(3));
				u.setPrice(rs.getString(4));
				u.setImg_1(rs.getString(5));
				u.setImg_2(rs.getString(6));
				comm.add(u);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return comm;
	}
	
	
	public List<User> findByName(String name) {
		List<User> list = new ArrayList<User>();
		String sql = "select * from t_user2 where name like ?";
		PreparedStatement stmt = null;
		try {
			stmt = DbUtil.getInstance().getConn().prepareStatement(sql);
			stmt.setString(1, "%"+name +"%");
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				User u = new User();
				u.setLoginId(rs.getString(1));
				u.setLoginPwd(rs.getString(2));
				u.setPhone(rs.getString(3));
				u.setEmail(rs.getString(4));
				u.setAddress(rs.getString(5));
				u.setMessage(rs.getString(6));
				list.add(u);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}
	
	public void save(User u){
		String sql="insert into t_user2 values(?,?,?,?,?,?,?)";
		PreparedStatement pst = null;
		try {
			pst = DbUtil.getInstance().getConn().prepareStatement(sql);
			pst.setString(1, u.getLoginId());
			pst.setString(2, u.getName());
			pst.setString(3, u.getLoginPwd());
			pst.setString(4, u.getPhone());
			pst.setString(5, u.getEmail());
			pst.setString(6, u.getAddress());
			pst.setString(7, u.getMessage());
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public void update(User u){
		String sql_update = "update t_user2 set name=?, password=?,phone=?,email=?,address=?,message=? where id=?";
		PreparedStatement pst = null;
		try {
			pst = DbUtil.getInstance().getConn().prepareStatement(sql_update);
			pst.setString(1, u.getName());
			pst.setString(2, u.getLoginPwd());
			pst.setString(3, u.getPhone());
			pst.setString(4, u.getEmail());
			pst.setString(5, u.getAddress());
			pst.setString(6, u.getMessage());
			pst.setString(7, u.getLoginId());
			
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public void update1(bsUser u){
		String sql_update = "update bs_user set name=?, password=? where id=?";
		PreparedStatement pst = null;
		try {
			pst = DbUtil.getInstance().getConn().prepareStatement(sql_update);
			pst.setString(1, u.getName());
			pst.setString(2, u.getLoginPwd());
			pst.setString(3, u.getLoginId());
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public void delete(String loginId){
		String sql = "delete from t_user2 where id=?";
		PreparedStatement pst = null;
		try {
			pst = DbUtil.getInstance().getConn().prepareStatement(sql);
			pst.setString(1, loginId);
			
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	  public static void main(String[] args) {
          int i = (int)Math.random()*2;
          System.out.println(i);
     }
	  
	  
	 public User findById(String loginId){
		 
		 String sql = "select * from t_user2 where id=?";
			User u =null;
			try {
				PreparedStatement pst = DbUtil.getInstance().getConn()
						.prepareStatement(sql);
				pst.setString(1, loginId);
				ResultSet rs = pst.executeQuery();

				
				if (rs.next()) {
					u = new User();
					u.setLoginId(rs.getString(1));
					u.setName(rs.getString(2));
					u.setLoginPwd(rs.getString(3));
					u.setPhone(rs.getString(4));
					u.setEmail(rs.getString(5));
					u.setAddress(rs.getString(6));
					u.setMessage(rs.getString(7));
					
				}
				rs.close();
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return u;
	 }
	 
public User findByname(String name){
		 
		 String sql = "select * from t_user2 where name=?";
			User u =null;
			try {
				PreparedStatement pst = DbUtil.getInstance().getConn()
						.prepareStatement(sql);
				pst.setString(1, name);
				ResultSet rs = pst.executeQuery();

				
				if (rs.next()) {
					u = new User();
					u.setLoginId(rs.getString(1));
					u.setName(rs.getString(2));
					u.setLoginPwd(rs.getString(3));
					u.setPhone(rs.getString(4));
					u.setEmail(rs.getString(5));
					u.setAddress(rs.getString(6));
					u.setMessage(rs.getString(7));
				}
				rs.close();
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return u;
	 }

public bsUser findByname1(String name){
	 
	 String sql = "select * from bs_user where name=?";
		bsUser u =null;
		try {
			PreparedStatement pst = DbUtil.getInstance().getConn()
					.prepareStatement(sql);
			pst.setString(1, name);
			ResultSet rs = pst.executeQuery();

			
			if (rs.next()) {
				u = new bsUser();
				u.setLoginId(rs.getString(1));
				u.setName(rs.getString(2));
				u.setLoginPwd(rs.getString(3));
			}
			rs.close();
			pst.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return u;
}

	

}


